﻿CREATE PROCEDURE [dbo].[System_PartitionTable]
	@TableName NVARCHAR(256),
	@PScheme NVARCHAR(256),
	@PartColumn NVARCHAR(256),
	@Debug TINYINT = 0
AS
	--The Easiest way to partition a table to drop it's clustered Index and recreate it on the partition scheme
	--For this to work the existing Clustered key must include the PartitionColumn which I don't think will be a problem at this point
	DECLARE @Return INT
	BEGIN TRY
		
		DECLARE @DropIndex NVARCHAR(MAX)
		DECLARE @CreatePartitionIndex NVARCHAR(MAX)
		DECLARE @PrintMessage NVARCHAR(MAX)
		DECLARE @IndexID INT

		DECLARE IDXCUR CURSOR FOR
		SELECT --st.name, si.*,
			si.index_id,
			CASE WHEN si.is_primary_key = 1 OR si.is_unique_constraint = 1 THEN
				N'ALTER TABLE ['+ ss.name +'].['+ st.name +'] DROP CONSTRAINT [' + si.name +']'
			ELSE N'DROP INDEX [' + si.name +'] ON ['+ ss.name +'].[' + st.name +']'
			END,
			CASE WHEN si.is_primary_key	= 0 AND si.is_unique_constraint =0 THEN	
				N'CREATE ' + CASE si.is_unique WHEN 1 THEN N'UNIQUE ' ELSE N'' END +
				si.type_desc  COLLATE SQL_Latin1_General_CP1_CI_AS + 
				 N'	INDEX [' + si.name +'] ON [' + ss.name + N'].[' + st.name + N'] '
			ELSE 
				N'ALTER TABLE [' + ss.name + N'].[' + st.name + N'] ADD CONSTRAINT [' +
				si.name + N']' + CASE WHEN si.is_primary_key = 1 THEN 'PRIMARY KEY' 
								WHEN si.is_unique_constraint =1 THEN 'UNIQUE' END +
				si.type_desc  COLLATE SQL_Latin1_General_CP1_CI_AS
		
			END  +'
		(#COLUMNS#
		) ON [' + @PScheme +']('+ @PartColumn +')'
			, 'Partitioning [' + ss.name +'].[' + st.name +'] - [' + si.name + ']'
		--SELECT *
		FROM sys.tables st
		INNER JOIN sys.schemas ss
		 on ss.schema_id = st.schema_id 
		INNER JOIN sys.indexes si
		 ON si.object_id = st.object_id 
		WHERE si.type_desc <> 'HEAP'
		AND st.name = @TableName 
		AND EXISTS (	SELECT 1
							FROM sys.columns sc1
							INNER JOIN sys.tables st1
							 ON st1.object_id = sc1.object_id 
							INNER JOIN sys.index_columns sic1 
							 ON st1.object_id = sic1.object_id 
							 AND sc1.column_id = sic1.column_id 
							WHERE st1.object_id = st.object_id 
							AND sic1.index_id = si.index_id  
							AND sc1.name = @PartColumn 
						)
		ORDER BY si.[type]

		OPEN IDXCUR 

		FETCH NEXT FROM IDXCUR INTO  @IndexID, @DropIndex, @CreatePartitionIndex, @PrintMessage

		WHILE @@FETCH_STATUS = 0
		BEGIN
			--Pull The Columns
			DECLARE @Columns NVARCHAR(MAX)
			SET @Columns = N''
		
			Select @Columns =@Columns +'
				' + sc.name + ' ' + CASE WHEN sic.is_descending_key = 1 
								THEN 'DESC'
								ELSE 'ASC' END +','
			FROM sys.tables st 
			INNER JOIN sys.indexes si
			 ON st.object_id = si.object_id 
			INNER JOIN sys.index_columns sic
			 ON sic.object_id = st.object_id 
			 AND SIC.index_id = si.index_id 
			INNER JOIN sys.columns sc
			 ON sc.object_id = st.object_id 
			 AND sc.column_id = sic.column_id 
			WHERE sic.key_ordinal > 0
			AND sic.is_included_column  = 0
			AND st.name = @TableName 
			AND si.index_id = @IndexID 
			AND EXISTS (	SELECT 1
							FROM sys.columns sc1
							INNER JOIN sys.tables st1
							 ON st1.object_id = sc1.object_id 
							INNER JOIN sys.index_columns sic1 
							 ON st1.object_id = sic1.object_id 
							 AND sc1.column_id = sic1.column_id 
							WHERE st1.object_id = st.object_id 
							AND sic1.index_id = si.index_id  
							AND sc1.name = @PartColumn 
						)
			ORDER BY sic.key_ordinal
		
		
			SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns) - 1)
			SET @CreatePartitionIndex = REPLACE(@CreatePartitionIndex, N'#COLUMNS#', @Columns )
		
			PRINT @PrintMessage

			BEGIN TRAN

			IF @Debug > 0
				PRINT @DropIndex
			
			EXEC sp_executesql @DropIndex

			IF @Debug > 0
				PRINT @CreatePartitionIndex
			
			EXEC sp_executesql @CreatePartitionIndex
			
			COMMIT TRAN 

			FETCH NEXT FROM IDXCUR INTO  @IndexID, @DropIndex, @CreatePartitionIndex, @PrintMessage
		END 
		SET @Return = 0
		
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN

		DECLARE @ErrMsg NVARCHAR(MAX)
		SET @ErrMsg = 'oisp_system_PartitionTable: ' + ERROR_MESSAGE()
		
		RAISERROR 65535 @ErrMsg

		SET @Return = 1
	END CATCH
	CLOSE IDXCUR
	DEALLOCATE IDXCUR

	RETURN @Return
